<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Log;
use App\Admin\ContractGaiyao;

class ContractGaiyaoController extends Controller
{
    /* 获取第三方待检样品 */
    public function getThirdDj(){
        $thirdCon = new \App\Http\Controllers\Admin\ThirdController();
        return $thirdCon->getThirdDj(); //成功返回添加的样品数量，不成功返回错误信息
    }

    /* 合同概要信息展示 */
    public function index(Request $request) {
        $contract_id = $request->id;
        $tool_info = DB::table('a_tool')->where('ishide', '0')->orderBy('stamp', 'stamp')->get();
        $data = DB::table('a_contract_gaiyao as a')
                  ->leftJoin('a_sample_info as b', 'a.id', '=', 'b.contract_gaiyao_id')
                  ->where('a.contract_id', $contract_id)
                  ->select('a.*', 'b.remark', 'b.contract_gaiyao_id')
                  ->orderBy('a.index', 'asc')->distinct()->get();
        $contractInfo = DB::table('a_contract')->find($contract_id);

        $setting_info = DB::table('setting_sys')->where('key', 'print_bar_code')->value('value'); //设置信息
        $setting_info = json_decode($setting_info, true);

        $enable_tool_ID = DB::table('setting')->where('id', '1')->value('enable_tool_ID');

        $barCode_JS_file = 'Common'; //前端加载的 打印条形码的 js 文件
        $app_name = config('constant.app_name');
        # 如果项目的条形码格式有个性化需求，就把这个项目的 APP_NAME 放到 $arr_app_name 数组中
        $arr_app_name = [
            "NeiMengWuHai",//内蒙乌海
            "HuiBangJianCe",//汇邦检测
            "SiChuanDeYang",//四川德阳
        ];
        if(in_array($app_name, $arr_app_name)) {
            $barCode_JS_file = $app_name;
        }
        $thirds = DB::table('a_third')->get();
        $remarkList = DB::table('a_remark_temp')->where('table', 'sample_info')->orderBy('sort', 'desc')->get();
        return view('admin.contractGaiyao.index', compact('remarkList', 'tool_info', 'contract_id', 'data', 'contractInfo', 'setting_info', 'barCode_JS_file', 'enable_tool_ID','thirds'));
    }

    /* 合同样品编辑 */
    public function edit(Request $request) {
        if($request->ajax()) {
            DB::transaction(function() use ($request) {
                $id = $request->id;
                $updata = $request->only('count', 'item_ids', 'item_names');
                $info = DB::table('a_contract_gaiyao')->find($id);
                if(isset($updata['count']) && $updata['count'] < $info->count) {
                    //bar_codes
                    $chacount = $info->count - $updata['count'];
                    $barCodesary = explode('～', $info->bar_codes);
                    $updata['bar_codes'] = $barCodesary[0].'～'.($barCodesary[1] - $chacount);
                    //获取表 a_sample_info 中数据
                    $sampleInfoAry = DB::table('a_sample_info')->whereBetween('bar_code', [$barCodesary[1] - $chacount + 1, $barCodesary[1]])->select('id')->get()->toArray();
                    $sampleinfoidsAry = array_column($sampleInfoAry, 'id');
                    //删除表 a_sample_info 中数据
                    DB::table('a_sample_info')->whereIn('id', $sampleinfoidsAry)->delete();
                    //删除表 a_item_data 中数据
                    DB::table('a_item_data')->whereIn('sample_info_id', $sampleinfoidsAry)->delete();
                    //删除表 a_input_data 中数据
                    DB::table('a_input_data')->whereIn('sample_info_id', $sampleinfoidsAry)->delete();
                    //更新合同概要数据表
                    DB::table('a_contract_gaiyao')->where('id', $id)->update($updata);
                }
            });
            return 1;
        }
        $id = $request->id;
        $info = DB::table('a_contract_gaiyao')->find($id);
        $sql = "SELECT
                    C.id,
                    C.`name`
                FROM
                    a_std_tool_item_rel AS A
                    LEFT JOIN a_testitem AS C ON A.testitem_id = C.id
                WHERE
                    A.std_id = :std_id
                    AND A.tool_id = :tool_id
                    AND A.ishide = 0
                    AND C.ishide = 0
                ORDER BY
                    A.sort ASC";
        $data_item = DB::select($sql, ['std_id' => $info->std_id, 'tool_id' => $info->tool_id]);
        return view('admin.contractGaiyao.edit', compact('info', 'data_item'));
    }

    /*合同样品编辑编号*/
    public function ajaxEditBarcode(Request $request) {
        $id = (int)$request->id;
        $numcha = (int)$request->numcha;
        $barcodes = $request->barcodes;
        $barcodesAry = explode('～', $barcodes);
        $beginbarcode = $barcodesAry[1] - $numcha + 1; //条形码开始值
        //检查是否是最后一批样品
        $sampleinfodata = DB::table('a_sample_info')->where('contract_id', $request->contract_id)->where('bar_code', '>', ($beginbarcode - 1))->orderBy('bar_code', 'desc')->get()->toArray();
        if($sampleinfodata === []) {
            return 1;
        }

        DB::beginTransaction();
        try {
            $gaiyao = [];
            $countsampleinfo = count($sampleinfodata);
            $numcha = $countsampleinfo < $numcha ? $countsampleinfo : $numcha;
            for($i = 0; $i < $numcha; $i ++) {
                $sampledata = array_shift($sampleinfodata);
                if(!in_array($sampledata->contract_gaiyao_id, $gaiyao)) {
                    array_push($gaiyao, $sampledata->contract_gaiyao_id);
                }
                $upSamData = ['bar_code' => ($beginbarcode + $i)];
                //如果第一次录入的条形码和现在的相同，就一起改了
                if($sampledata->bar_code_1st == $sampledata->bar_code){
                    $upSamData['bar_code_1st'] = ($beginbarcode + $i);
                }
                //更新 a_sample_info 表中的 bar_code
                DB::table('a_sample_info')->where('id', $sampledata->id)->update($upSamData);
            }
            //更新 a_contract_gaiyao 表中的 bar_codes 数据
            foreach($gaiyao as $gaiyao_id) {
                $gaiyao = DB::table('a_sample_info')->where('contract_gaiyao_id', $gaiyao_id)->select('bar_code')->get()->toArray();
                $barcodess = array_column($gaiyao, 'bar_code');

                //判断条形码是否连续并分组
                $fenzu = $this->SerialNumber($barcodess);
                //                dd($fenzu);
                if(count($fenzu) === 1) {
                    $minbarcode = min($barcodess);
                    $maxbarcode = max($barcodess);
                    DB::table('a_contract_gaiyao')->where('id', $gaiyao_id)->update([
                        'bar_codes' => $minbarcode.'～'.$maxbarcode,
                        'index'     => intval(substr($minbarcode, - 3))
                    ]);
                }
                else {
                    //修改旧合同的数量
                    $barcodefenzu = $fenzu[0];
                    $minbarcode = min($barcodefenzu);
                    $maxbarcode = max($barcodefenzu);
                    DB::table('a_contract_gaiyao')->where('id', $gaiyao_id)->update([
                        'bar_codes' => $minbarcode.'～'.$maxbarcode,
                        'index'     => intval(substr($minbarcode, - 3)),
                        'count'     => count($barcodefenzu)
                    ]);
                    //新建合同概要数据
                    $newgaiyao = DB::table('a_contract_gaiyao')->find($gaiyao_id);
                    unset($newgaiyao->id);
                    $barcodefenzu = $fenzu[1];
                    $minbarcode = min($barcodefenzu);
                    $maxbarcode = max($barcodefenzu);
                    $newgaiyao->bar_codes = $minbarcode.'～'.$maxbarcode;
                    $newgaiyao->index = intval(substr($minbarcode, - 3));
                    $newgaiyao->count = count($barcodefenzu);
                    $newgaiyao = (array)$newgaiyao;
                    $newgaiyaoid = DB::table('a_contract_gaiyao')->insertGetId($newgaiyao);
                    //修改 a_sample_info 中的 contract_gaiyao_id 字段值
                    DB::table('a_sample_info')->whereBetween('bar_code', [$minbarcode, $maxbarcode])->update(['contract_gaiyao_id' => $newgaiyaoid]);
                    //修改 a_item_data 中的 contract_gaiyao_id 字段值
                    //获取修改过的 a_sample_info 中的id
                    $sampleids = DB::table('a_sample_info')->whereBetween('bar_code', [$minbarcode, $maxbarcode])->select('id')->get()->toArray();
                    $sampleids = array_column($sampleids, 'id');
                    DB::table('a_item_data')->whereIn('sample_info_id', $sampleids)->update(['contract_gaiyao_id' => $newgaiyaoid]);
                }
            }
            DB::commit();
        } catch(\Exception $e) {
            DB::rollBack();
            return $e->getMessage().$e->getLine();
        }
        return 1;
    }

    private function SerialNumber($numary = []) {
        $num = [];
        $count = count($numary);
        sort($numary);
        if($count > 1) {
            $temp = 0;
            for($i = 0; $i < $count - 1; $i ++) {
                if($i === 0) {
                    $num[$temp][] = $numary[$i];
                }
                $j = $i + 1;
                $temp = $numary[$j] - $numary[$i] === 1 ? $temp : 1 - $temp;
                $num[$temp][] = $numary[$j];
            }
        }
        else {
            return $numary;
        }
        return $num;
    }

    /* 合同概要信息展示，不可编辑 */
    public function show(Request $request) {
        $contract_id = $request->id;
        $data = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->orderBy('id', 'desc')->get();
        $contractInfo = DB::table('a_contract')->select('client_name')->find($contract_id);

        return view('admin.contractGaiyao.show', compact('data', 'contractInfo'));
    }

    /* 合同概要信息展示，不可编辑 */
    public function viewShow(Request $request) {
        $contract_id = $request->id;
        $data = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->orderBy('index', 'asc')->get();
        $contractInfo = DB::table('a_contract')->select('id', 'client_name', 'batchNum')->find($contract_id);


        return view('admin.contractGaiyao.viewShow', compact('data', 'contractInfo'));
    }

    //添加检验项目
    public function addItem(Request $request) {
        //请求为 ajax
        if($request->expectsJson()) {
            $inputData = $request->only(['id', 'tool_id', 'item_id']);
            DB::beginTransaction();
            try {
                //当前概要内容
                $gyInfo = DB::table('a_contract_gaiyao')->find($inputData['id']);
                //当前项目id
                $itemIds = explode(',', $gyInfo->item_ids);
                if(in_array($inputData['item_id'], $itemIds)) {
                    return out(0, '该项目已存在！');
                }
                array_push($itemIds, $inputData['item_id']);

                //修改概要检测项目信息
                $arr_item_names = DB::table('a_testitem as a')->leftJoin('a_std_tool_item_rel as b', 'b.testitem_id', '=', 'a.id')->whereIn('a.id', $itemIds)->orderBy('b.sort', 'asc')->pluck('a.name', 'a.id')->toArray();
                $upGYData = [];
                $upGYData['item_ids'] = implode(',', array_keys($arr_item_names));
                $upGYData['item_names'] = implode('，', $arr_item_names);
                DB::table('a_contract_gaiyao')->where('id', $inputData['id'])->update($upGYData);

                //添加检测项目信息
                $bar_codes = explode('～', $gyInfo->bar_codes);
                $sampleInfoIds = DB::table('a_sample_info')->whereBetween('bar_code', [$bar_codes[0], $bar_codes[1]])->pluck('id');
                $testdataKeyIds = DB::table('a_testdata_key')->where(['std_id'=>$gyInfo->std_id,'tool_id'=>$gyInfo->tool_id,'item_id'=>$inputData['item_id']])->pluck('id');
                $data_item = $data_input = [];
                foreach($sampleInfoIds as $sampleInfoId) {
                    //构建a_item_data要插入的数据
                    $data_item[] = [
                        'contract_id'        => $gyInfo->contract_id,
                        'contract_gaiyao_id' => $gyInfo->id,
                        'sample_info_id'     => $sampleInfoId,
                        'item_id'            => $inputData['item_id'],
                        'station_ids'        => DB::table('a_std_tool_item_rel')->where(['tool_id' => $inputData['tool_id'], 'testitem_id' => $inputData['item_id']])->value('station_ids'),
                    ];

                    //构建a_input_data要插入的数据
                    foreach($testdataKeyIds as $testdataKeyId){
                        $data_input[] = [
                            'sample_info_id' => $sampleInfoId,
                            'testdata_key_id' => $testdataKeyId,
                        ];
                    }

                }
                //a_item_data插入数据    insert 返回true 或 false
                DB::table('a_item_data')->insert($data_item);
                //a_input_data插入数据
                DB::table('a_input_data')->insert($data_input);
                DB::commit();
            } catch(\Throwable $e) {
                DB::rollBack();
                return out(0, '添加失败！'.$e->getMessage());
            }
            return out(0, '添加成功！');
        }
    }

    /* 合同概要信息添加 */
    public function add() {
        //请求为 ajax
        //if(request()->expectsJson()) {
            // dd(request()->all());
            //还有after_id,items,money
            $request = request();
            $data = $request->only(['contract_id', 'remark', 'tool_id', 'vol_id', 'class_id', 'std_id', 'count', 'sub_count']);
            $after_id = $request->after_id; //插入标志，告诉服务器这条数据插在谁之后
            $otherAddCols = ['money','cycle'];
            foreach ($otherAddCols as $col) {
                $val = $request->$col;
                if ($val) {
                    $data[$col] = $val;
                }
            }

            //项目id
            $items = $request->items;
            $data['item_ids'] = implode(',', $items); //1,2,3,4  数组变字符串
            //项目名称
            $arr_item_names = DB::table('a_testitem')->whereIn('id', $items)->orderBy(DB::raw("field(id,{$data['item_ids']})"))->pluck('name');
            $data['item_names'] = implode("，", $arr_item_names->toArray()); //此处是中文 逗号
            // 如果额定电压id为 0
            if($data['vol_id'] === '0') {
                $data['voltage'] = '/';
            }
            else {
                $data['voltage'] = DB::table('a_voltage_class')->where('id', $data['vol_id'])->value('val');
            }
            // 如果分类id为 0
            if($data['class_id'] === '0') {
                $data['class'] = '/';
            }
            else {
                $data['class'] = DB::table('a_voltage_class')->where('id', $data['class_id'])->value('val');
            }
            //标准代号
            $data['std_num'] = DB::table('a_standard')->where('id', $data['std_id'])->value('num');
            //样品信息
            $tool_info = DB::table('a_tool')->where('id', $data['tool_id'])->first();
            //样品名称
            $data['tool_name'] = $tool_info->name;
            //样品单位
            $data['unit'] = $tool_info->unit;

            //将MySQL的事务隔离级别改为串行
            // $sql="SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE";
            // DB::select($sql);

            //查询批次的编号
            $batch_num = DB::table('a_contract')->where('id', $data['contract_id'])->value('batchNum');
            //锁表
            // $sql = "LOCK TABLE a_contract_gaiyao WRITE";
            // DB::select($sql);
            // DB::transaction(function () use ($data,$batch_num) {

            /* 制作条码开始索引------------------------start */
            $res1 = DB::table('a_contract_gaiyao')->where('id', $after_id)->first();
            //1.在gaiyao表中记录不为空
            if($res1) {
                $data['index'] = $res1->index + $res1->count;
            }
            else {
                //2.在gaiyao表中记录为空
                $data['index'] = 1;
            }
            /* 制作条码开始索引----------------------- end */

            //格式化字符串
            $chars_count = DB::table('setting')->where('id', '1')->value('chars_count');
            $format_string = "%0{$chars_count}d";
            /* 制作条码范围-------------------------start */
            //开始的条码
            $str_start = $batch_num.sprintf($format_string, $data['index']);
            //结尾的条码
            $str_end = $batch_num.sprintf($format_string, $data['index'] + $data['count'] - 1);
            $data['bar_codes'] = $str_start.'～'.$str_end;
            /* 制作条码范围-----------------------------end */
            // dd($data);
            $third_id = request('third_id',0);
            if ($third_id) { //如果是其他系统导入的数据，返回gaiyao_id
                $data['third_id'] = $third_id;
                $gyId = $this->addGyData($data, $items, $batch_num, $format_string);
                return $gyId;
            }else{
                DB::beginTransaction();
                try {
                    $gyId = $this->addGyData($data, $items, $batch_num, $format_string);
                    DB::commit();
                } catch(\Throwable $e) {
                    // dd($e->getMessage());
                    DB::rollBack();
                    return '添加失败:'.$e->getMessage();
                }
                return 1;
            }
        //}
    }
    /*将新增到表 单独出来，因为其他系统的数据要把增加概要和样品信息做整体事务处理*/
    private function addGyData($data, $items, $batch_num, $format_string){
        //插入数据    insert 返回true 或 false
        $contract_gaiyao_id = DB::table('a_contract_gaiyao')->insertGetId($data);
        // foreach ($data_gaiyao as $val) {
        $count = $data['count'];
        $index = $data['index'];
        $sub_count = $data['sub_count']; //一套样品包含的数量，例如一套绝缘杆包含3节
        // $srt_item = $val->item_ids;

        $arr_item = $items;
        $itemDataCon = new \App\Http\Controllers\Admin\ItemDataController();
        for($i = 0; $i < $count; $i ++) {
            $str_suf = sprintf($format_string, $index ++); //样品号的后几位
            for($j = 1; $j <= $sub_count; $j ++) {
                if($sub_count > 1) {
                    # 如果一套样品包含多件，则加后缀
                    $barcode_suf = "-$j";
                }
                else {
                    # code...
                    $barcode_suf = "";
                }
                $bar_code = $batch_num.$str_suf.$barcode_suf;
                //构建要插入的数据
                $data_sample_insert = [
                    'bar_code'           => $bar_code,
                    'bar_code_1st'       => $bar_code,   //新增的和bar_code一样
                    'remark'             => $data['remark'],
                    'contract_id'        => $data['contract_id'],
                    'contract_gaiyao_id' => $contract_gaiyao_id,
                    'key'                => str_random(6) //用六位随机字符串作为密码
                ];
                //插入数据    insertGetId 返回 ID 值
                $sampleInfoId = DB::table('a_sample_info')->insertGetId($data_sample_insert);
                //a_testdata_key因为系统中有19年的残存数据，所以这里要和a_std_tool_item_rel联查
                $testdataKeyIds = DB::table('a_testdata_key as a')
                    ->leftJoin('a_std_tool_item_rel as b','a.std_tool_item_rel_id','=','b.id')
                    ->where(['b.std_id'=>$data['std_id'],'b.tool_id'=>$data['tool_id'],'a.ishide'=>0])
                    ->whereIn('b.testitem_id',explode(',',$data['item_ids']))
                    ->orderBy('a.sort')
                    ->pluck('a.id');
                $data_item = $data_input = [];
                //构建a_item_data插入数据    insert 返回true 或 false
                foreach($arr_item as $vv) {
                    $inItem = [
                        'contract_id'        => $data['contract_id'],
                        'contract_gaiyao_id' => $contract_gaiyao_id,
                        'sample_info_id'     => $sampleInfoId,
                        'item_id'            => $vv,
                        'station_ids'        => DB::table('a_std_tool_item_rel')->where(['tool_id' => $data['tool_id'], 'testitem_id' => $vv])->value('station_ids'),
                        'res' => 2,
                        'utime' => date('Y-m-d H:i:s',time()),
                    ];
                    $data_item[] = $inItem;
                }
                DB::table('a_item_data')->insert($data_item);

                //构建a_input_data要插入的数据
                foreach($testdataKeyIds as $testdataKeyId){
                    $data_input[] = [
                        'sample_info_id' => $sampleInfoId,
                        'testdata_key_id' => $testdataKeyId,
                    ];
                }
                DB::table('a_input_data')->insert($data_input);
            }
        }
        return $contract_gaiyao_id;
    }

    /* 合同概要信息删除 */
    public function del(Request $request) {
        $id = $request->input('id');
        //交给模型处理数据  delete 返回受影响的行数
        $result = DB::table('a_contract_gaiyao')->where('id', $id)->delete();
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result >= 0 ? '1' : '0';
    }

    /* 合同的概要信息编辑展示(任务分配) */
    public function indexAssignTask(Request $request) {
        if($request->expectsJson()) {
            $data = ContractGaiyao::where('contract_id', $request->id)->get();
            $jsonData['data'] = $data;
            return response()->json($jsonData);
        }
        else {
            //查询出 状态为启用的 主检人
            $where = [
                ['istester', '=', '1'],
                ['status', '=', '2'],
            ];
            if(!isAdmin()) {
                $where[] = ['id', '!=', 1];
            }
            $data_tester = DB::table('manager')->where($where)->get();

            //批次id
            $batch_id = $request->id;
            $contractInfo = DB::table('a_contract')->select('client_name')->find($batch_id);

            return view('admin.contractGaiyao.indexAssignTask', compact('data_tester', 'batch_id', 'contractInfo'));
        }
    }

    //获取所有分配人员
    private function getAssignManagers($tester_id,$fj_testers){
        if(!$fj_testers){
            echo '<script>layer.msg("请先指定副检人！");</script>';
            die;
        }
        $manager = DB::table('manager')->where('id',$tester_id)->select('partner','realname')->first();
        foreach($fj_testers as $k => $val){
            if($val == ""){
                $fj_testers[$k] = $manager->partner;
            }
        }
        array_unshift($fj_testers,$manager->realname);
        $managers = implode('，',array_unique($fj_testers));
        return $managers;
    }

    //单个任务分配
    public function oneAssign(Request $request) {
        // dd($request->all());
        $gaiyao_id = $request->id;
        $tester_id = $request->tester_id;
        //dd($gaiyao_id);
        $item_id = $request->checkID;  //勾选的检测项目id
        $managers = $this->getAssignManagers($tester_id,$request->fj_testers);
        // dd($managers);
        //  update 返回受影响的行数
        $res1 = DB::table('a_item_data')->where('contract_gaiyao_id', $gaiyao_id)->whereIn('item_id', $item_id)->update(['manager_id' => $tester_id,'managers'=>$managers]);

        //设置初始分配时间
        DB::table('a_item_data')->where('contract_gaiyao_id', $gaiyao_id)->whereIn('item_id', $item_id)->whereNull('allot_time')->update(['allot_time' => date('Y-m-d H:i:s')]);

        $sql = "SELECT
                    manager.realname
                FROM
                (
                    SELECT DISTINCT
                        a_item_data.manager_id
                    FROM
                        a_item_data
                    WHERE
                        contract_gaiyao_id = :gaiyao_id
                ) AS tabA
                LEFT JOIN manager ON tabA.manager_id = manager.id";
        $data_testers = DB::select($sql,['gaiyao_id'=>$gaiyao_id]);

        $arr_name = [];
        foreach($data_testers as $val) {
            $arr_name[] = $val->realname;
        }
        // dd($arr_name);
        $str_name = implode("，", $arr_name); //数组 转 字符串，中文逗号

        $res2 = DB::table('a_contract_gaiyao')->where('id', $gaiyao_id)->update(['managers' => $str_name]);

        $this->updateContractStatus($request->batch_id);
        $itemDataCon = new \App\Http\Controllers\Admin\ItemDataController();
        $sampleIds = DB::table('a_sample_info')->where('contract_gaiyao_id',$gaiyao_id)->pluck('id');
        foreach($sampleIds as $sampleInfoId){
            $itemDataCon->updateTester($sampleInfoId);  //更新 a_sample_info 表的 试验人员
        }
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $res1 >= 0 ? '1' : '0';
        // return $res1 >= 0 && $res2 >= 0 ? '1' : '0';
    }

    private function updateContractStatus($batch_id) {
        // 分配任务后检查未分配的记录数
        $res3 = DB::table('a_item_data')->where('contract_id', $batch_id)->where('manager_id', '0')->count();
        //如果为0，说明已全部分配完，此时改变contract的状态
        if($res3 === 0) {
            $res4 = DB::table('a_contract')->where('id', $batch_id)->update(['status' => '5']);

            //查询此批次是否记录过检测开始的时间
            $test_start = DB::table('a_status_time')->where('contract_id', $batch_id)->value('test_start');
            if(!$test_start) {
                # 如果没记录过，就记录检测开始的时间
                $now_time = date("Y/m/d H:i:s"); //当前时间
                DB::table('a_status_time')->where('contract_id', $batch_id)->update(['test_start' => $now_time]);
            }
        }
    }

    //一键自动分配任务
    public function autoAssign(Request $request) {

        $contract_id = $request->contract_id; //批次的id
        $sql = "SELECT
                    B.*,
                    C.std_id,
                    C.tool_id,
                    C.std_num,
                    C.tool_name,
                    D.manager_id,
                    E.`status`,
                    E.istester,
                    E.id AS id_manager_tab,
                    F.`name` AS item_name
                FROM
                    ( SELECT DISTINCT A.contract_id, A.contract_gaiyao_id, A.item_id FROM a_item_data AS A WHERE A.contract_id = :contract_id ) AS B
                    LEFT JOIN a_contract_gaiyao AS C ON B.contract_gaiyao_id = C.id
                    LEFT JOIN a_std_tool_item_rel AS D ON D.std_id = C.std_id
                    AND D.tool_id = C.tool_id
                    AND D.testitem_id = B.item_id
                    LEFT JOIN manager AS E ON E.id = D.manager_id
                    LEFT JOIN a_testitem AS F ON F.id = B.item_id";

        $assign_data = DB::select($sql, ['contract_id' => $contract_id]);

        foreach($assign_data as $val) {
            if(!($val->id_manager_tab)) {
                # id_manager_tab 为null 时，说明未绑定主检人
                return "{$val->std_num} → {$val->tool_name} → {$val->item_name}<br>未绑定主检人";
            }
            if($val->istester === 0) {
                # istester =0，说明此人现在不是主检人
                return "{$val->std_num} → {$val->tool_name} → {$val->item_name}<br>绑定的人员当前已不是主检人";
            }
            if($val->status === '3') {
                # status = 3，说明此人为 禁用 状态
                return "{$val->std_num} → {$val->tool_name} → {$val->item_name}<br>绑定的人员为“禁用”状态";
            }

            //修改分配人员
            DB::table('a_item_data')->where([
                ['contract_gaiyao_id', '=', $val->contract_gaiyao_id],
                ['item_id', '=', $val->item_id]
            ])->update(['manager_id' => $val->id_manager_tab]);

            //设置初始分配时间
            DB::table('a_item_data')->where([
                ['contract_gaiyao_id', '=', $val->contract_gaiyao_id],
                ['item_id', '=', $val->item_id]
            ])->whereNull('allot_time')->update(['allot_time' => date('Y-m-d H:i:s')]);

            $sql = "SELECT
                        manager.realname
                    FROM
                    (
                        SELECT DISTINCT
                            a_item_data.manager_id
                        FROM
                            a_item_data
                        WHERE
                            contract_gaiyao_id = :contract_gaiyao_id
                    ) AS tabA
                    LEFT JOIN manager ON tabA.manager_id = manager.id";

            $data_testers = DB::select($sql, ['contract_gaiyao_id' => $val->contract_gaiyao_id]);

            $arr_name = [];
            foreach($data_testers as $vv) {
                $arr_name[] = $vv->realname;
            }

            $str_name = implode("，", $arr_name); //数组 转 字符串，中文逗号

            DB::table('a_contract_gaiyao')->where('id', $val->contract_gaiyao_id)->update(['managers' => $str_name]);
        }

        // 分配任务后检查未分配的记录数
        $res3 = DB::table('a_item_data')->where('contract_id', $contract_id)->where('manager_id', '0')->count();
        //如果为0，说明已全部分配完，此时改变contract的状态
        if($res3 === 0) {
            DB::table('a_contract')->where('id', $contract_id)->update(['status' => '5']);

            //查询此批次是否记录过检测开始的时间
            $test_start = DB::table('a_status_time')->where('contract_id', $request->batch_id)->value('test_start');
            if(!$test_start) {
                # 如果没记录过，就记录检测开始的时间
                $now_time = date("Y/m/d H:i:s"); //当前时间
                DB::table('a_status_time')->where('contract_id', $request->batch_id)->update(['test_start' => $now_time]);
            }
        }

        return "成功";
    }

    //批量任务分配
    public function batchAssign(Request $request) {
        $tester_id = $request->tester_id;
        $gaiyao_id = $request->checkID;  //数组,勾选的概要ids
        $managers = $this->getAssignManagers($tester_id,$request->fj_testers);
        // dd($managers);
        DB::beginTransaction();
        try {
            //更新详细试验记录
            DB::table('a_item_data')->whereIn('contract_gaiyao_id', $gaiyao_id)->update(['manager_id' => $tester_id,'managers'=>$managers]);

            //更新概要表中的检测人员
            $str_name = DB::table('manager')->where('id', $tester_id)->value('realname');
            DB::table('a_contract_gaiyao')->whereIn('id', $gaiyao_id)->update(['managers' => $str_name]);

            //检测并更新合同状态
            $this->updateContractStatus($request->batch_id);
            $itemDataCon = new \App\Http\Controllers\Admin\ItemDataController();
            $sampleIds = DB::table('a_sample_info')->where('contract_gaiyao_id',$gaiyao_id)->pluck('id');
            foreach($sampleIds as $sampleInfoId){
                $itemDataCon->updateTester($sampleInfoId);  //更新 a_sample_info 表的 试验人员
            }
            DB::commit();
        } catch(\Exception $e) {
            DB::rollBack();
            return out(0, $e->getMessage().$e->getLine());
        }

        //由于框架自身不支持响应bool值，所以转化一种形式
        return '1';
    }

    /**
     * 切换标准关联标准
     */
    public function changeStdAjax() {
        $toolId = request()->tool_id;
        $stdId = request()->std_id;
        if(!$toolId || !$stdId) {
            return '0';
        }
        if(request()->isMethod('post')) {
            $id = request()->id;
            $toStdId = request()->to_std_id;
            if(!$id || !$toStdId) {
                return '0';
            }
            $gaiyao = DB::table('a_contract_gaiyao')->find($id);
            //判断工器具的检测项目是否有没有映射的关系
            $testItemIds = explode(',', $gaiyao->item_ids);
            $noRelation = DB::table('a_std_tool_item_rel')->where(['std_id' => $stdId, 'tool_id' => $toolId, 'id_map' => 0])->whereIn('testitem_id', $testItemIds)->exists();
            if($noRelation) {
                return '当前样品项目标准缺少映射关系！';
            }
            $stdToolItemRelIds = DB::table('a_std_tool_item_rel')->where(['std_id' => $stdId, 'tool_id' => $toolId])->whereIn('testitem_id', $testItemIds)->pluck('id');
            //判断所有的映射关系的小映射是否完全
            $noRelationDetail = DB::table('a_testdata_key')->where(['std_id' => $stdId, 'tool_id' => $toolId, 'id_map' => 0])->whereIn('item_id', $testItemIds)->whereIn('std_tool_item_rel_id', $stdToolItemRelIds)->exists();
            if($noRelationDetail) {
                return '当前样品项目标准缺少详细的映射关系！';
            }
            DB::beginTransaction();   //开始事务
            try {
                //获取修改的标准编号
                $toStdNum = DB::table('a_standard')->where('id', $toStdId)->value('num');

                /*修改数据表*/
                $itemIdRelation = DB::table('a_std_tool_item_rel as a')
                                    ->leftJoin('a_std_tool_item_rel as b', 'a.id_map', '=', 'b.id')
                                    ->where(['a.std_id' => $stdId, 'a.tool_id' => $toolId])->whereIn('a.testitem_id', $testItemIds)
                                    ->pluck('b.testitem_id as map_item_id', 'a.testitem_id')->toArray();  //获取关系映射id

                $itemNamesRelation = DB::table('a_std_tool_item_rel as a')
                                       ->leftJoin('a_testitem as b', 'a.testitem_id', '=', 'b.id')
                                       ->whereIn('b.id', $itemIdRelation)->orderBy('a.sort', 'asc')
                                       ->pluck('b.name', 'b.id')->toArray();  //获取关系映射name
                //                dd($testItemIds,$itemIdRelation,$itemNamesRelation);
                //修改概要标准,std_id，std_num，item_ids，item_names
                DB::table('a_contract_gaiyao')->where('id', $id)->update(['std_id' => $toStdId, 'std_num' => $toStdNum, 'item_ids' => implode(',', $itemIdRelation), 'item_names' => implode('，', $itemNamesRelation)]);
                //修改 更新 a_sample_info 表的下次试验时间
                $cycle = DB::table('a_std_tool_rel')->where(['std_id' => $toStdId, 'tool_id' => $toolId])->value('cycle');
                $cyclemonth = intval($cycle*12);
                $sampleInfoIds = DB::table('a_sample_info')->where('contract_gaiyao_id', $id)->pluck('id');
                //                DB::connection()->enableQueryLog();#开启执行日志
                foreach($sampleInfoIds as $sampleInfoId) {
                    $testDate = DB::table('a_sample_info')->where('id', $sampleInfoId)->value('test_time');
                    $nextTIME = strtotime('+'.$cyclemonth.' months', strtotime($testDate));
                    $nextTIME = date('Y-m-d', strtotime('-1 day', $nextTIME));
                    DB::table('a_sample_info')->where('id', $sampleInfoId)->update(['next_time' => $nextTIME]);
                }
                //                dd(DB::getQueryLog());
                //修改 样品每个试验项目的数据表 a_item_data item_id
                foreach($itemIdRelation as $itemId => $mapItemId) {
                    if($itemId !== $mapItemId) {
                        DB::table('a_item_data')->where(['contract_gaiyao_id' => $id, 'item_id' => $itemId])->update(['item_id' => $mapItemId]);
                    }
                }

                //修改 详细数据中的映射 a_input_data testdata_key_id
                $keyIdRelation = DB::table('a_testdata_key')->where(['std_id' => $stdId, 'tool_id' => $toolId])->whereIn('item_id', $testItemIds)->whereIn('std_tool_item_rel_id', $stdToolItemRelIds)->orderBy('sort')->pluck('id_map', 'id');
                foreach($keyIdRelation as $id => $idMap) {
                    DB::table('a_input_data')->where(['testdata_key_id' => $id])->whereIn('sample_info_id', $sampleInfoIds)->update(['testdata_key_id' => $idMap]);
                }
                //4.修改item_id
                DB::commit();
            } catch(\Exception $exception) {
                DB::rollBack();
                return $exception->getMessage();
            }
            return 1;
        }
        $data = DB::table('a_std_tool_rel as a')->leftJoin('a_standard as b', 'b.id', '=', 'a.std_id')
                  ->where('a.tool_id', $toolId)->where('a.std_id', '!=', $stdId)->where('b.ishide', 0)
                  ->select('a.std_id', 'b.num')->get();
        return $data;
    }

    /**
     * 修改分类
     */
    public function changeClassAjax() {
        if(request()->isMethod('post')) {
            $data = request()->only(['class_id', 'class', 'id']);
            $res = DB::table('a_contract_gaiyao')->where('id', $data['id'])->update($data);
            return intval($res);
        }
    }

    /* 批量结果录入 */
    public function resLuru($contract_id) {
        if(request()->expectsJson()) {
            $data = ContractGaiyao::where('contract_id', $contract_id)->get();
            $jsonData['data'] = $data;
            return response()->json($jsonData);
        }
        else {
            //查询出 状态为启用的 主检人
            $where = [
                ['istester', '=', '1'],
                ['status', '=', '2'],
            ];
            if(!isAdmin()) {
                $where[] = ['id', '!=', 1];
            }
            $data_tester = DB::table('manager')->where($where)->get();

            //批次id
            $batch_id = $contract_id;
            $contractInfo = DB::table('a_contract')->select('client_name')->find($batch_id);
            return view('admin.contractGaiyao.resLuru', compact('data_tester', 'batch_id', 'contractInfo'));
        }
    }

    /* 批量结果录入 --- 获取单项目信息*/
    public function itemData() {
        $param = request()->only('gyId', 'itemId');
        $data = [];
        //概要
        $gy = DB::table('a_contract_gaiyao')->find($param['gyId']);
        //检验标准---条件
        $data['condition'] = DB::table('a_condition as a')
                               ->leftJoin('a_condition_key as b', 'a.condition_key_id', '=', 'b.id')
                               ->where(['b.std_id' => $gy->std_id, 'tool_id' => $gy->tool_id, 'item_id' => $param['itemId']])
                               ->whereIn('a.vol_class_id', array_unique([$gy->vol_id, $gy->class_id]))
                               ->select('key', 'unit', 'val')
                               ->get();
        //检验标准---要求
        $data['yaoqiu'] = DB::table('a_std_tool_item_rel')->where(['std_id' => $gy->std_id, 'tool_id' => $gy->tool_id, 'testitem_id' => $param['itemId']])->value('jishu_yaoqiu');
        //分配时间
        $data['allot_time'] = DB::table('a_item_data')
                                ->where(['contract_gaiyao_id' => $gy->id, 'item_id' => $param['itemId']])
                                ->selectRaw("CONCAT(min(allot_time), ' 至 ', max(allot_time)) AS allot_time")
                                ->value('allot_time');
        // 原始记录--试验数据
        $data['testdata_key'] = DB::table('a_testdata_key')->where(['std_id' => $gy->std_id, 'tool_id' => $gy->tool_id, 'item_id' => $param['itemId']])->select('id', 'data_key', 'data_unit')->orderBy('sort')->get();

        return out(1, 'ok', $data);
    }

    /* 批量结果录入 --- 提交单项目信息*/
    public function itemDataSub(Request $request) {
        if(!request('utime')) {
            return out(0, '试验时间必填！');
        }
        if(isset($request->testKey)) {
            if(count(array_filter($request->testKey)) !== count($request->testKey)) {
                return out(0, '试验数据必填！');
            }
        }
        else {
            $request->testKey = [];
        }

        $gy = DB::table('a_contract_gaiyao')->find($request->gyId);
        $sampIds = DB::table('a_sample_info')->where('contract_gaiyao_id', $request->gyId)->pluck('id')->toArray();
        $itemDataCon = new ItemDataController();
        $itemId = $request->item_id;
        $cycle = DB::table('a_std_tool_rel')->where(['std_id' => $gy->std_id, 'tool_id' => $gy->tool_id])->value('cycle');
        request()->offsetSet('cycle',$cycle);
        request()->offsetSet('old_station_ids','');
        request()->offsetSet('station_ids','');
        Log::info(auth('admin')->user()->realname.'批量录入:'.json_encode($request->all()));
        foreach($sampIds as $sampId) {
            $input_data_ids = DB::table('a_input_data')->where(['sample_info_id' => $sampId])->whereIn('testdata_key_id', array_keys($request->testKey))->pluck('testdata_key_id', 'id');
            $input_data_val = [];
            foreach($input_data_ids as $id => $testdata_key_id) {
                $input_data_val[$id] = $request->testKey[$testdata_key_id];
            }
            request()->offsetSet('val',$input_data_val);
            $item_id = DB::table('a_item_data')->where(['sample_info_id' => $sampId, 'item_id' => $itemId])->value('id');
            request()->offsetSet('item_id',$item_id);
            request()->offsetSet('id',$sampId);
            $itemDataCon->resLuru($request, $sampId);
        }
        return 1;
    }
}
